package com.wframe.project.jnd.dao;

import java.util.*;

import com.wframe.project.jnd.vo.Coupon;
import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;

import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.frame.util.DateConvert;
import com.wframe.framework.database.BaseDao;

@Repository
public class CouponDao extends BaseDao {

    public PageResult queryCoupon(JdataBean parms, int size, int pageno) {
        String sql = "select c.*,TIMESTAMPDIFF(SECOND,now(), enddate) diffday from wb_coupon c where userid =? and ( source = ? or source is null or source = 0 ) and tag=1";
        List parmlist = new ArrayList();
        String nowday = DateConvert.toString(new Date());
        parmlist.add(parms.getString("userid"));
        parmlist.add(parms.getInt("source"));
        if (parms.getInt("state") == 3) {
            sql += (" and enddate<'" + nowday + "'");
        } else {
            sql += " and state=" + parms.getInt("state");
        }
        if (parms.getInt("state") == 1) {
            int containBefore = parms.getInt("containBefore");
            if (containBefore == 1) {
                sql = sql + " and enddate>='" + nowday + "'";
            } else {
                sql = sql + " and startdate<='" + nowday + "' and enddate>='" + nowday + "'";
            }
        }
        if (StringUtils.isNotBlank(parms.getString("ordermoney"))) {
            sql += " and min_money<=" + parms.getDouble("ordermoney");
        }
        sql += " order by create_time desc";
        return querySqlMap2(sql, parmlist, size, pageno);
    }

    /**
     * 计算优惠券各条件数量
     *
     * @return
     */
    public List<JdataBean> countCoupon(String userid) {
        String sql = "SELECT typedata.type,CAST(COUNT(*) AS char) As typecount FROM (SELECT IF (NOW() < enddate AND state = 1,1,IF (startdate <= NOW() AND state = 2,2,IF ( NOW() >= enddate AND state = 1,3,0 ))) AS type FROM wb_coupon WHERE userid = ? AND tag = 1 ) typedata GROUP BY typedata.type";
        return querySql(sql, new Object[]{userid});
    }

    public List<JdataBean> getUsableCount(String userid, int source) {
        String sql = "SELECT " +
                " COUNT(coup.id) AS counUsableCoupon " +
                " FROM " +
                " wb_coupon coup " +
                " LEFT JOIN ( " +
                " SELECT " +
                "  COUNT(get_couponid) num, " +
                "  cou.get_couponid " +
                " FROM " +
                "  wb_coupon cou " +
                " WHERE " +
                "  cou.tag = 1 " +
                " AND cou.userid = ? " +
                " GROUP BY " +
                "  cou.get_couponid " +
                ") cou ON cou.get_couponid = coup.id " +
                " WHERE " +
                " coup.tag = 0 " +
                " AND coup.state = 1 " +
                " AND ( " +
                " coup.source IS NULL " +
                " OR coup.source = 0 " +
                " OR coup.source = ? " +
                " ) " +
                " AND ( " +
                " NOW() BETWEEN coup.startdate " +
                " AND coup.enddate " +
                " ) " +
                " AND coup.amount > coup.sales " +
                " AND coup.limit_num > IFNULL(cou.num, 0) " +
                " ORDER BY " +
                " coup.id DESC ";
        return querySql(sql, new Object[]{userid, source});
    }


    public PageResult queryDisCoupon(JdataBean parms, int size, int pageno) {
        String sql = "select c.*,TIMESTAMPDIFF(SECOND,now(), enddate) diffday from wb_coupon c where userid =? and ( source = ? or source is null or source = 0 ) and tag=1 and state=1 ";
        List parmlist = new ArrayList();
        String nowday = DateConvert.toString(new Date());
        parmlist.add(parms.getString("userid"));
        parmlist.add(parms.getInt("source"));

        sql = sql + " and ( ";
        sql = sql + " ( ";
        sql = sql + " startdate > '" + nowday + "' and min_money<=" + parms.getDouble("ordermoney");
        sql = sql + " ) ";
        sql = sql + " or ";
        sql = sql + " ( ";
        sql = sql + " min_money > " + parms.getDouble("ordermoney") + " and startdate <= '" + nowday + "' " + " and enddate > '" + nowday + "' ";
        sql = sql + " ) ";
        sql = sql + " ) ";

        sql += " order by create_time desc";
        return querySqlMap2(sql, parmlist, size, pageno);
    }

    public JdataBean getCouponByCode(String code) {
        return getObject("select * from wb_coupon where code=?", new Object[]{code});
    }

    public JdataBean getCoupon(String id) throws Exception {
        return getObject("select * from wb_coupon where id=?", new Object[]{id});
    }

    public void tagCouponUsed(String id) throws Exception {
        JdataBean get = getCoupon(id);
        updateCouponState(id, 2);
        setCouponusenum(get.getString("get_couponid"));
    }

    public void updateCouponState(String id, int state) throws Exception {
        execute("update wb_coupon set state=? where id=?", new Object[]{state, id});
    }

    public void setCouponusenum(String id) throws Exception {
        execute("update wb_coupon set usenum=(ifnull(usenum,0)+1) where id=?", new Object[]{id});
    }

    public List<Map> findAll(JdataBean parms) {
        String sql = "select c.* from wb_coupon c where userid =? and (source =? or source is null) and tag=1 and state=1 and startdate<=? and enddate>=? and min_money order by create_time desc";
        String nowday = DateConvert.toString(new Date());
        return mapList(sql, new Object[]{parms.getString("userid"), parms.getInt("source"), nowday, nowday});
    }

    public boolean isUsable(String couponid, String userid, int source, double paymoney) {
        String sql = "SELECT EXISTS(SELECT 1 from wb_coupon WHERE userid=? and id=? and tag=1 and state=1 and (source = ? or source is null ) and startdate<=? and enddate>=? and min_money<=?) isExist";
        String now = DateConvert.toString(new Date());

        Object isExist = uniqueColumnResult(sql, new Object[]{userid, couponid, source, now, now, paymoney});
        return Integer.parseInt(String.valueOf(isExist)) == 1;
    }

    public boolean isExist(String userid, double payMoney, int ordinal) {
        String sql = "SELECT EXISTS(select 1 from wb_coupon c where userid =? and (source =? or source is null) and tag=1 and state=1 and startdate<=? and enddate>=? and min_money<=?) isExist";
        String now = DateConvert.toString(new Date());
        Object isExist = uniqueColumnResult(sql, new Object[]{userid, ordinal, now, now, payMoney});
        return Integer.parseInt(String.valueOf(isExist)) == 1;
    }

    public PageResult getUsableCoupon(JdataBean parms, int size, int pno) {
        String sql = "SELECT " +
                " coup.id AS couponid, " +
                " coup.offer_money offerMoney, " +
                " coup.`name`, " +
                " coup.explanation, " +
                " coup.startdate startDate, " +
                " coup.enddate endDate, " +
                " coup.limit_num AS time, " +
                " cast(IFNULL(cou.num,0) as char) AS coup_sales " +
                "FROM " +
                " wb_coupon coup " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  COUNT(get_couponid) num, " +
                "  cou.get_couponid " +
                " FROM " +
                "  wb_coupon cou " +
                " WHERE " +
                "  cou.tag = 1 " +
                " AND cou.userid = ? " +
                " GROUP BY " +
                "  cou.get_couponid " +
                ") cou ON cou.get_couponid = coup.id " +
                "WHERE " +
                " coup.tag = 0 " +
                "AND coup.state = 1 " +
                "AND ( " +
                " coup.source IS NULL " +
                " OR coup.source = 0 " +
                " OR coup.source = ? " +
                ") " +
                "AND ( " +
                " NOW() BETWEEN coup.startdate " +
                " AND coup.enddate " +
                ") " +
                "AND coup.amount > coup.sales " +
                "AND coup.limit_num > IFNULL(cou.num, 0) " +
                "ORDER BY " +
                " coup.id DESC";
        List parmlist = new ArrayList();
        if (parms != null) {
            parmlist.add(parms.getString("userid"));
            parmlist.add(parms.getString("source"));
        }
        return querySqlMap2(sql, parmlist, size, pno);
    }

    public List<Coupon> findReceivedCoupons(String userId, int platform) {
        String sql = "SELECT " +
                " coup.offer_money offerMoney, " +
                " coup.`name`, " +
                " coup.min_money minMoney, " +
                " UNIX_TIMESTAMP(coup.startdate) * 1000 startDate, " +
                " UNIX_TIMESTAMP(coup.enddate) * 1000 endDate " +
                "FROM " +
                " wb_coupon coup " +
                "WHERE " +
                " coup.min_money>coup.offer_money " +
                "AND coup.state = 1 " +
                "AND coup.userid = ? " +
                "AND ( " +
                " NOW() BETWEEN coup.startdate " +
                " AND coup.enddate " +
                ") " +
                "AND ( " +
                " coup.source IS NULL " +
                " OR coup.source = 0 " +
                " OR coup.source = ? " +
                ") " +
                "ORDER BY " +
                " coup.id DESC";
        SQLQuery query = createBeanResultSQLQuery(sql, new Object[]{userId, platform}, Coupon.class);
        query.addScalar("name");
        query.addScalar("offerMoney");
        query.addScalar("minMoney");
        query.addScalar("startDate", StandardBasicTypes.LONG);
        query.addScalar("endDate", StandardBasicTypes.LONG);
        return getObject(query.list());
    }

    public List<Coupon> findAvailableCoupons(String userId, int platform) {
        String sql = "SELECT coup.id, " +
                " coup.offer_money offerMoney, " +
                " coup.min_money minMoney, " +
                " coup.`name`, " +
                " UNIX_TIMESTAMP(coup.startdate)*1000 startDate, " +
                " UNIX_TIMESTAMP(coup.enddate)*1000 endDate, " +
                " DATEDIFF(coup.enddate, NOW()) remain, " +
                " coup.activity_day is not null active " +
                "FROM " +
                " wb_coupon coup " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  COUNT(get_couponid) num, " +
                "  cou.get_couponid " +
                " FROM " +
                "  wb_coupon cou " +
                " WHERE cou.userid = ? AND cou.get_source=3 " +
                " GROUP BY " +
                "  cou.get_couponid " +
                ") cou ON cou.get_couponid = coup.id " +
                "WHERE " +
                " coup.tag = 0 " +
                "AND coup.state = 1 " +
                "AND ( " +
                " coup.source IS NULL " +
                " OR coup.source = 0 " +
                " OR coup.source = ? " +
                ") " +
                "AND ( " +
                " NOW() BETWEEN coup.startdate " +
                " AND coup.enddate " +
                ") " +
                "AND ( " +
                " NOW() BETWEEN coup.receive_starttime " +
                " AND coup.receive_endtime " +
                ") " +
                "AND coup.min_money > coup.offer_money " +
                "AND coup.amount > coup.sales " +
                "AND coup.receive=1 " +
                "AND coup.limit_num > IFNULL(cou.num, 0) " +
                "ORDER BY " +
                " coup.id DESC";
        SQLQuery query = createBeanResultSQLQuery(sql, new Object[]{userId, platform}, Coupon.class);
        query.addScalar("id");
        query.addScalar("offerMoney");
        query.addScalar("name");
        query.addScalar("minMoney");
        query.addScalar("startDate", StandardBasicTypes.LONG);
        query.addScalar("endDate", StandardBasicTypes.LONG);
        query.addScalar("remain", StandardBasicTypes.INTEGER);
        query.addScalar("active", StandardBasicTypes.BOOLEAN);
        return getObject(query.list());
    }

    public List<Coupon> findAll(String userid, String productid, int platform) {
        String sql = "SELECT " +
                " coup.id, " +
                " coup.`name`, " +
                " coup.offer_money offerMoney, " +
                " coup.min_money minMoney, " +
                " UNIX_TIMESTAMP(coup.startdate) * 1000 startDate, " +
                " UNIX_TIMESTAMP(coup.enddate) * 1000 endDate " +
                "FROM " +
                " wb_promotions prom " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  pac.productid, " +
                "  pc.id " +
                " FROM " +
                "  wb_products_and_class pac " +
                " JOIN wb_products_class pc ON ( " +
                "  pac.classcode = pc.`code` " +
                "  OR pac.classcode LIKE CONCAT(pc.`code`, '.%') " +
                " ) " +
                " WHERE " +
                "  pac.productid = ? " +
                ") pac ON prom.product_val = pac.id " +
                "JOIN wb_coupon coup ON ( " +
                " prom.id = coup.promotionid " +
                " AND coup.amount > coup.sales " +
                " AND coup.min_money > coup.offer_money " +
                " AND coup.tag = 0 " +
                " AND coup.state = 1 " +
                " AND ( " +
                "  NOW() BETWEEN coup.startdate " +
                "  AND coup.enddate " +
                " ) " +
                " AND coup.receive = 1 " +
                " AND ( " +
                "  NOW() BETWEEN coup.receive_starttime " +
                "  AND coup.receive_endtime " +
                " ) " +
                " AND ( " +
                "  coup.source = 0 " +
                "  OR coup.source = ? " +
                " ) " +
                ") " +
                " left JOIN ( " +
                " SELECT " +
                "  COUNT(get_couponid) num, " +
                "  cou.get_couponid " +
                " FROM " +
                "  wb_coupon cou " +
                " WHERE " +
                "  cou.userid = ? " +
                " AND cou.get_source = 3 " +
                " GROUP BY " +
                "  cou.get_couponid " +
                ") cou ON coup.id = cou.get_couponid " +
                "WHERE " +
                " prom.promotion_type = 4 " +
                " AND coup.limit_num > IFNULL(cou.num, 0) "+
                "AND ( " +
                " prom.product_type = 0 " +
                " OR ( " +
                "  prom.product_type = 1 " +
                "  AND prom.product_val = ? " +
                " ) " +
                " OR ( " +
                "  prom.product_type = 3 " +
                "  AND prom.product_val = pac.id " +
                " ) " +
                ") " +
                "GROUP BY " +
                " prom.id " +
                "ORDER BY " +
                " NULL";
        SQLQuery query = createBeanResultSQLQuery(sql, new Object[]{productid, platform, userid, productid}, Coupon.class);
        query.addScalar("id");
        query.addScalar("name");
        query.addScalar("offerMoney");
        query.addScalar("minMoney");
        query.addScalar("startDate", StandardBasicTypes.LONG);
        query.addScalar("endDate", StandardBasicTypes.LONG);
        return getObject(query.list());
    }

    public JdataBean findOne(String userId, Integer couponId, int platform) {
        String sql = "SELECT " +
                " coup.*, IFNULL(cou.num, 0) num " +
                "FROM " +
                " wb_coupon coup " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  COUNT(get_couponid) num, " +
                "  cou.get_couponid " +
                " FROM " +
                "  wb_coupon cou " +
                " WHERE " +
                "  cou.userid = ? " +
                " AND cou.get_couponid = ? " +
                " AND cou.get_source = 3 " +
                ") cou ON (coup.id = cou.get_couponid AND coup.limit_num > IFNULL(cou.num, 0))" +
                "WHERE " +
                " coup.id = ? " +
                "AND coup.tag = 0 " +
                "AND coup.state = 1 " +
                "AND coup.amount > coup.sales " +
                "AND ( " +
                " NOW() BETWEEN coup.startdate " +
                " AND coup.enddate " +
                ") " +
                "AND ( " +
                " NOW() BETWEEN coup.receive_starttime " +
                " AND coup.receive_endtime " +
                ") " +
                "AND coup.receive = 1 " +
                "AND coup.min_money > coup.offer_money " +
                "AND ( " +
                " coup.source IS NULL " +
                " OR coup.source = 0 " +
                " OR coup.source = ? " +
                ")";
        return uniqueResult(sql, new Object[]{userId, couponId, couponId, platform});
    }

    public void updateSales(Object id) {
        execute("update wb_coupon set sales=sales+1 WHERE id=?", new Object[]{id});
    }

    public List<Coupon> findOrderCoupons(Map parms) {
        String sql = "SELECT " +
                " coup.id, " +
                " coup.`name`, " +
                " coup.offer_money offerMoney, " +
                " coup.min_money minMoney, " +
                " coup.limit_num > IFNULL(cou.num, 0) available " +
                "FROM " +
                " wb_products prod " +
                "JOIN ( " +
                " SELECT " +
                "  pac.productid, " +
                "  pc.id " +
                " FROM " +
                "  wb_products_and_class pac " +
                " LEFT JOIN wb_products_class pc ON ( " +
                "  pac.classcode = pc.`code` " +
                "  OR pac.classcode LIKE CONCAT(pc.`code`, '.%') " +
                " ) " +
                " WHERE " +
                "  pac.productid IN (:ids) " +
                ") pac ON prod.id = pac.productid " +
                "JOIN wb_promotions prom ON ( " +
                " prom.promotion_type = 4 " +
                " AND ( " +
                "  prom.product_type = 0 " +
                "  OR ( " +
                "   prom.product_type = 1 " +
                "   AND prom.product_val = prod.id " +
                "  ) " +
                "  OR ( " +
                "   prom.product_type = 3 " +
                "   AND prom.product_val = pac.id " +
                "  ) " +
                " ) " +
                ") " +
                "JOIN wb_coupon coup ON ( " +
                " prom.id = coup.promotionid " +
                " AND coup.min_money > coup.offer_money " +
                " AND coup.amount > coup.sales " +
                " AND coup.tag = 0 " +
                " AND coup.state = 1 " +
                " AND ( " +
                "  NOW() BETWEEN coup.startdate " +
                "  AND coup.enddate " +
                " ) " +
                " AND coup.receive = 1 " +
                " AND ( " +
                "  NOW() BETWEEN coup.receive_starttime " +
                "  AND coup.receive_endtime " +
                " ) " +
                " AND ( " +
                "  coup.source IS NULL " +
                "  OR coup.source = 0 " +
                "  OR coup.source = :platform " +
                " ) " +
                ") " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  COUNT(get_couponid) num, " +
                "  cou.get_couponid " +
                " FROM " +
                "  wb_coupon cou " +
                " WHERE " +
                "  cou.userid = :userid " +
                " AND cou.get_source = 3 " +
                " GROUP BY " +
                "  cou.get_couponid " +
                ") cou ON coup.id = cou.get_couponid " +
                "WHERE " +
                " prod.id IN (:ids) " +
                "GROUP BY " +
                " prom.id " +
                "ORDER BY " +
                " NULL";
        SQLQuery query = createBeanResultSQLQuery(sql, parms, Coupon.class);
        query.addScalar("id");
        query.addScalar("name");
        query.addScalar("offerMoney");
        query.addScalar("minMoney");
        query.addScalar("available", StandardBasicTypes.BOOLEAN);
        return getObject(query.list());
    }
}